using System;
using System.Web;
using System.Web.Caching;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Globalization;
using SunRiseSoft.Library.Configuration;

namespace SunRiseSoft.Library.DataAccess
{

	public class Database
	{

#region "Property"
		private string _connectionName;
		private string _connectionString;
		private DbProviderFactory _dbProviderFactory;
		protected DataInstrumentation _instrumentation;

		protected internal string ConnectionString {
			get { return _connectionString; }
		}

		public DbProviderFactory DbProviderFactory {
			get { return _dbProviderFactory; }
		}
#endregion

#region "Construction"
		protected Database(string connectionName, string connectionString, DbProviderFactory dbProviderFactory)
		{
			if (string.IsNullOrEmpty(connectionName)) {
				throw new ArgumentException("connectionName");
			}
			if (string.IsNullOrEmpty(connectionString)) {
				throw new ArgumentException("connectionString");
			}
			if ((dbProviderFactory == null)) {
				throw new ArgumentNullException("dbProviderFactory");
			}
			_connectionName = connectionName;
			_connectionString = connectionString;
			_dbProviderFactory = dbProviderFactory;
			_instrumentation = DataInstrumentation.Create(_connectionName);
		}

        protected Database(System.Configuration.ConnectionStringSettings connectionStringSetting)
            : this(connectionStringSetting.Name, connectionStringSetting.ConnectionString, DbProviderFactories.GetFactory(connectionStringSetting.ProviderName))
        {
        }

        public Database()
            : this(ConfigurationManager.Connection)
        {
        }

        public Database(string connectionName)
            : this(ConfigurationManager.Connections[connectionName])
        {
        }

        public static Database CreateDatabase(string connectionName, string connectionString)
		{
            return new Database(connectionName, connectionString, OleDbFactory.Instance);
		}

        public static Database CreateDatabase(string connectionString)
		{
            return CreateDatabase("DefaultName", connectionString);
		}
#endregion
#region "GetConnection"
		public virtual DbConnection CreateConnection()
		{
			DbConnection conn = _dbProviderFactory.CreateConnection();
			conn.ConnectionString = _connectionString;
			return conn;
		}

		internal DbConnection GetNewOpenConnection()
		{
			DbConnection conn = null;
			try {
				try {
					conn = CreateConnection();
					conn.Open();
					_instrumentation.ConnectionOpened(conn);
				}
				catch (Exception exception) {
					_instrumentation.ConnectionFailed(conn, exception);
					throw;
				}
			}
			catch {
				if ((conn != null)) {
					conn.Close();
				}
				throw;
			}
			return conn;
		}

		protected ConnectionWrapper GetOpenConnection()
		{
			return GetOpenConnection(true);
		}

		protected ConnectionWrapper GetOpenConnection(bool disposeInnerConnection)
		{
			DbTransaction trans = TransactionControl.GetTransaction(this);
			if ((trans != null)) {
				return new ConnectionWrapper(trans);
			}
			else {
				return new ConnectionWrapper(GetNewOpenConnection(), disposeInnerConnection);
			}
		}
#endregion
#region "GetCommand"
		public DbCommand GetSqlStringCommand(string sqlString)
		{
			if (string.IsNullOrEmpty(sqlString)) {
				throw new ArgumentException("sqlString");
			}
			return CreateCommandByCommandType(CommandType.Text, sqlString);
		}

		private DbCommand CreateCommandByCommandType(CommandType commandType, string commandText)
		{
			DbCommand command = _dbProviderFactory.CreateCommand();
			command.CommandType = commandType;
			command.CommandText = commandText;
			return command;
		}

		public virtual DbCommand GetStoredProcCommand(string storedProcedureName)
		{
			if (string.IsNullOrEmpty(storedProcedureName)) {
				throw new ArgumentException("storedProcedureName");
			}
			return CreateCommandByCommandType(CommandType.StoredProcedure, storedProcedureName);
		}

		public virtual DbCommand GetStoredProcCommand(string storedProcedureName, params object[] parameterValues)
		{
			DbCommand command = GetStoredProcCommand(storedProcedureName);
			DiscoverParameters(command);
			AssignParameterValues(command, parameterValues);
			return command;
		}

		public void DiscoverParameters(DbCommand command)
		{
			//Search DbParameter from cache first
			object obj = HttpContext.Current.Cache[command.CommandText];
			if (obj != null) {
				List<IDataParameter> cachePms = (List<IDataParameter>)obj;
				foreach (DbParameter pm in cachePms) {
					IDataParameter cloneParameter = (IDataParameter)((ICloneable)pm).Clone();
					command.Parameters.Add(cloneParameter);
				}
				return;
			}

			List<IDataParameter> pms = new List<IDataParameter>();
			using (DbConnection conn = GetNewOpenConnection()) {
				using (DbCommand discoveryCommand = CreateCommandByCommandType(command.CommandType, command.CommandText)) {
					discoveryCommand.Connection = conn;
					DeriveParameters(discoveryCommand);
					foreach (IDataParameter parameter in discoveryCommand.Parameters) {
						IDataParameter cloneParameter = (IDataParameter)((ICloneable)parameter).Clone();
						IDataParameter cloneParameterClone = (IDataParameter)((ICloneable)parameter).Clone();
						command.Parameters.Add(cloneParameter);
						pms.Add(cloneParameterClone);
					}
				}
			}
			//Cache for Discover DbParameter
            HttpContext.Current.Cache.Add(command.CommandText, pms, null, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(10), CacheItemPriority.Normal, null);
		}

		protected virtual void DeriveParameters(DbCommand discoveryCommand)
		{
		}

		protected virtual int UserParametersStartIndex {
			get { return 0; }
		}

		private void AssignParameterValues(DbCommand command, object[] values)
		{
			for (int i = 0; i <= values.Length - 1; i++) {
				IDataParameter parameter = command.Parameters[i + UserParametersStartIndex];

				if (values[i] == null) {
					parameter.Value = DBNull.Value;
				}
				else {
					parameter.Value = values[i];
				}
			}
		}

		//Public Overridable Sub SetParameterValue(ByVal command As DbCommand, ByVal parameterName As String, ByVal value As Object)
		//    Dim tmp As Object
		//    If value Is Nothing Then
		//        tmp = DBNull.Value
		//    Else
		//        tmp = value
		//    End If
		//    command.Parameters(BuildParameterName(parameterName)).Value = tmp
		//End Sub

		//Public Function GetStoredProcCommandWithSourceColumns(ByVal storedProcedureName As String, ByVal ParamArray sourceColumns As String()) As DbCommand
		//    If String.IsNullOrEmpty(storedProcedureName) Then
		//        Throw New ArgumentException("storedProcedureName")
		//    End If
		//    If (sourceColumns Is Nothing) Then
		//        Throw New ArgumentNullException("sourceColumns")
		//    End If
		//    Dim storedProcCommand As DbCommand = Me.GetStoredProcCommand(storedProcedureName)
		//    Using connection As DbConnection = Me.CreateConnection
		//        storedProcCommand.Connection = connection
		//        Me.DiscoverParameters(storedProcCommand)
		//    End Using
		//    Dim index As Integer = 0
		//    Dim parameter As IDataParameter
		//    For Each parameter In storedProcCommand.Parameters
		//        If ((parameter.Direction = ParameterDirection.Input) Or (parameter.Direction = ParameterDirection.InputOutput)) Then
		//            parameter.SourceColumn = sourceColumns(index)
		//            index += 1
		//        End If
		//    Next
		//    Return storedProcCommand
		//End Function
#endregion
#region "PrepareParameter"
		public virtual string BuildParameterName(string name)
		{
			return name;
		}

		protected virtual bool SameNumberOfParametersAndValues(DbCommand command, object[] values)
		{
			int numberOfParametersToStoredProcedure = command.Parameters.Count;
			int numberOfValuesProvidedForStoredProcedure = values.Length;
			return numberOfParametersToStoredProcedure == numberOfValuesProvidedForStoredProcedure;
		}

		public virtual object GetParameterValue(DbCommand command, string name)
		{
			return command.Parameters[BuildParameterName(name)].Value;
		}

		protected DbParameter CreateParameter(string name)
		{
			DbParameter param = _dbProviderFactory.CreateParameter();
			param.ParameterName = BuildParameterName(name);
			return param;
		}

		protected DbParameter CreateParameter(string name, DbType dbType, int size, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
		{
			DbParameter param = CreateParameter(name);
			param.DbType = dbType;
			param.Size = size;
			if (value == null) {
				param.Value = DBNull.Value;
			}
			else {
				param.Value = value;
			}
			param.Direction = direction;
			param.SourceColumn = sourceColumn;
			param.SourceVersion = sourceVersion;
			return param;
		}

		//Protected Overridable Sub ConfigureParameter(ByVal param As DbParameter, ByVal name As String, ByVal dbType As DbType, ByVal size As Integer, ByVal direction As ParameterDirection, ByVal nullable As Boolean, ByVal precision As Byte, ByVal scale As Byte, ByVal sourceColumn As String, ByVal sourceVersion As DataRowVersion, ByVal value As Object)
		//    param.DbType = dbType
		//    param.Size = size
		//    If value Is Nothing Then
		//        param.Value = DBNull.Value
		//    Else
		//        param.Value = value
		//    End If
		//    param.Direction = direction
		//    param.IsNullable = nullable
		//    param.SourceColumn = sourceColumn
		//    param.SourceVersion = sourceVersion
		//End Sub

		//Private Sub AssignParameterValues(ByVal command As DbCommand, ByVal values As Object())
		//    Dim num As Integer = Me.UserParametersStartIndex
		//    Dim i As Integer
		//    For i = 0 To values.Length - 1
		//        Dim parameter As IDataParameter = command.Parameters.Item((i + num))
		//        Me.SetParameterValue(command, parameter.ParameterName, values(i))
		//    Next i
		//End Sub

		//Public Overridable Sub SetParameterValue(ByVal command As DbCommand, ByVal parameterName As String, ByVal value As Object)
		//    If value Is Nothing Then
		//        command.Parameters.Item(Me.BuildParameterName(parameterName)).Value = DBNull.Value
		//    Else
		//        command.Parameters.Item(Me.BuildParameterName(parameterName)).Value = value
		//    End If
		//End Sub

		//Protected Overridable Function SameNumberOfParametersAndValues(ByVal command As DbCommand, ByVal values As Object()) As Boolean
		//    Dim count As Integer = command.Parameters.Count
		//    Dim length As Integer = values.Length
		//    Return (count = length)
		//End Function

		//Protected MustOverride Sub DeriveParameters(ByVal discoveryCommand As DbCommand)

		//Public Sub DiscoverParameters(ByVal command As DbCommand)
		//    Using wrapper As ConnectionWrapper = Me.GetOpenConnection
		//        Using discoveryCommand As DbCommand = Me.CreateCommandByCommandType(command.CommandType, command.CommandText)
		//            discoveryCommand.Connection = wrapper.Connection
		//            'Me.DeriveParameters(discoveryCommand)
		//            Dim parameter As IDataParameter
		//            For Each parameter In discoveryCommand.Parameters
		//                Dim parameter2 As IDataParameter = DirectCast(DirectCast(parameter, ICloneable).Clone, IDataParameter)
		//                command.Parameters.Add(parameter2)
		//            Next
		//        End Using
		//    End Using
		//End Sub
#endregion
#region "AddParameter"
		//Public Function AddInParameter(ByVal command As DbCommand, ByVal name As String, ByVal dbType As DbType) As DbParameter
		//    Return AddInParameter(command, name, dbType, 0)
		//End Function

		//Public Function AddInParameter(ByVal command As DbCommand, ByVal name As String, ByVal dbType As DbType, ByVal size As Integer) As DbParameter
		//    Return AddInParameter(command, name, dbType, size, Nothing)
		//End Function

		public DbParameter AddInParameter(DbCommand command, string name, DbType dbType, object value)
		{
			return AddParameter(command, name, dbType, 0, ParameterDirection.Input, value);
		}

		public DbParameter AddInParameter(DbCommand command, string name, DbType dbType, int size, object value)
		{
			return AddParameter(command, name, dbType, size, ParameterDirection.Input, value);
		}

		//Public Function AddInOutParameter(ByVal command As DbCommand, ByVal name As String, ByVal dbType As DbType) As DbParameter
		//    Return AddParameter(command, name, dbType, 0, ParameterDirection.InputOutput, Nothing)
		//End Function

		//Public Function AddInOutParameter(ByVal command As DbCommand, ByVal name As String, ByVal dbType As DbType, ByVal size As Integer) As DbParameter
		//    Return AddParameter(command, name, dbType, size, ParameterDirection.InputOutput, Nothing)
		//End Function

		public DbParameter AddInOutParameter(DbCommand command, string name, DbType dbType, object value)
		{
			return AddParameter(command, name, dbType, 0, ParameterDirection.InputOutput, value);
		}

		public DbParameter AddInOutParameter(DbCommand command, string name, DbType dbType, int size, object value)
		{
			return AddParameter(command, name, dbType, size, ParameterDirection.InputOutput, value);
		}

		public DbParameter AddOutParameter(DbCommand command, string name, DbType dbType)
		{
			return AddParameter(command, name, dbType, 0, ParameterDirection.Output, null);
		}

		public DbParameter AddOutParameter(DbCommand command, string name, DbType dbType, int size)
		{
			return AddParameter(command, name, dbType, size, ParameterDirection.Output, null);
		}

		public DbParameter AddReturnParameter(DbCommand command, string name, DbType dbType)
		{
			return AddParameter(command, name, dbType, 0, ParameterDirection.ReturnValue, null);
		}

		public DbParameter AddReturnParameter(DbCommand command, string name, DbType dbType, int size)
		{
			return AddParameter(command, name, dbType, size, ParameterDirection.ReturnValue, null);
		}

		public DbParameter AddParameter(DbCommand command, string name, DbType dbType, int size, ParameterDirection direction, object value)
		{
			return AddParameter(command, name, dbType, size, direction, string.Empty, DataRowVersion.Default, value);
		}


		public DbParameter AddInParameter(DbCommand command, string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
		{
			return AddParameter(command, name, dbType, 0, ParameterDirection.Input, sourceColumn, sourceVersion);
		}

		public DbParameter AddInParameter(DbCommand command, string name, DbType dbType, int size, string sourceColumn, DataRowVersion sourceVersion)
		{
			return AddParameter(command, name, dbType, size, ParameterDirection.Input, sourceColumn, sourceVersion);
		}

		public DbParameter AddInOutParameter(DbCommand command, string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
		{
			return AddParameter(command, name, dbType, 0, ParameterDirection.InputOutput, sourceColumn, sourceVersion);
		}

		public DbParameter AddInOutParameter(DbCommand command, string name, DbType dbType, int size, string sourceColumn, DataRowVersion sourceVersion)
		{
			return AddParameter(command, name, dbType, size, ParameterDirection.InputOutput, sourceColumn, sourceVersion);
		}

		public DbParameter AddOutParameter(DbCommand command, string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
		{
			return AddParameter(command, name, dbType, 0, ParameterDirection.Output, sourceColumn, sourceVersion);
		}

		public DbParameter AddOutParameter(DbCommand command, string name, DbType dbType, int size, string sourceColumn, DataRowVersion sourceVersion)
		{
			return AddParameter(command, name, dbType, size, ParameterDirection.Output, sourceColumn, sourceVersion);
		}

		public DbParameter AddParameter(DbCommand command, string name, DbType dbType, int size, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion)
		{
			return AddParameter(command, name, dbType, size, direction, string.Empty, DataRowVersion.Default, null);
		}


		public DbParameter AddParameter(DbCommand command, string name, DbType dbType, int size, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
		{
			DbParameter parameter = CreateParameter(name, dbType, size, direction, sourceColumn, sourceVersion, value);
			command.Parameters.Add(parameter);
			return parameter;
		}
#endregion
#region "PrepareCommand"
		protected static void PrepareCommand(DbCommand command, ConnectionWrapper wrapper)
		{
			if ((command == null)) {
				throw new ArgumentNullException("command");
			}
			if ((wrapper == null)) {
				throw new ArgumentNullException("wrapper");
			}
			command.Connection = wrapper.Connection;
			command.Transaction = wrapper.Transaction;
		}

		//Protected Shared Sub PrepareCommand(ByVal command As DbCommand, ByVal transaction As DbTransaction)
		//    If (command Is Nothing) Then
		//        Throw New ArgumentNullException("command")
		//    End If
		//    If (transaction Is Nothing) Then
		//        Throw New ArgumentNullException("transaction")
		//    End If
		//    command.Connection = transaction.Connection
		//    command.Transaction = transaction
		//End Sub
#endregion
#region "ExecuteNonQuery"
		public virtual int ExecuteNonQuery(CommandType commandType, string commandText)
		{
			using (DbCommand command = CreateCommandByCommandType(commandType, commandText)) {
				return ExecuteNonQuery(command);
			}
		}
		public virtual int ExecuteNonQuery(DbCommand command)
		{
			using (ConnectionWrapper wrapper = GetOpenConnection()) {
				PrepareCommand(command, wrapper);
				return DoExecuteNonQuery(command);
			}
		}



		//Public Overridable Function ExecuteNonQuery(ByVal command As DbCommand, ByVal transaction As DbTransaction) As Integer
		//    PrepareCommand(command, transaction)
		//    Return Me.DoExecuteNonQuery(command)
		//End Function

		//Public Overridable Function ExecuteNonQuery(ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As Integer
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteNonQuery(command)
		//    End Using
		//End Function

		//Public Overridable Function ExecuteNonQuery(ByVal transaction As DbTransaction, ByVal commandType As CommandType, ByVal commandText As String) As Integer
		//    Using command As DbCommand = Me.CreateCommandByCommandType(commandType, commandText)
		//        Return Me.ExecuteNonQuery(command, transaction)
		//    End Using
		//End Function

		//Public Overridable Function ExecuteNonQuery(ByVal transaction As DbTransaction, ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As Integer
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteNonQuery(command, transaction)
		//    End Using
		//End Function
#endregion
#region "ExecuteScalar"
		public virtual object ExecuteScalar(CommandType commandType, string commandText)
		{
			using (DbCommand command = CreateCommandByCommandType(commandType, commandText)) {
				return ExecuteScalar(command);
			}
		}

		public virtual object ExecuteScalar(DbCommand command)
		{
			if ((command == null)) {
				throw new ArgumentNullException("command");
			}
			using (ConnectionWrapper wrapper = GetOpenConnection()) {
				PrepareCommand(command, wrapper);
				return DoExecuteScalar(command);
			}
		}

		//Public Overridable Function ExecuteScalar(ByVal command As DbCommand, ByVal transaction As DbTransaction) As Object
		//    PrepareCommand(command, transaction)
		//    Return Me.DoExecuteScalar(command)
		//End Function

		//Public Overridable Function ExecuteScalar(ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As Object
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteScalar(command)
		//    End Using
		//End Function

		//Public Overridable Function ExecuteScalar(ByVal transaction As DbTransaction, ByVal commandType As CommandType, ByVal commandText As String) As Object
		//    Using command As DbCommand = Me.CreateCommandByCommandType(commandType, commandText)
		//        Return Me.ExecuteScalar(command, transaction)
		//    End Using
		//End Function

		//Public Overridable Function ExecuteScalar(ByVal transaction As DbTransaction, ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As Object
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteScalar(command, transaction)
		//    End Using
		//End Function
#endregion
#region "ExecuteReader"
		public DbDataReader ExecuteReader(CommandType commandType, string commandText)
		{
			using (DbCommand command = CreateCommandByCommandType(commandType, commandText)) {
				return ExecuteReader(command);
			}
		}

		public virtual DbDataReader ExecuteReader(DbCommand command)
		{

			ConnectionWrapper wrapper = GetOpenConnection(false);
			try {
				PrepareCommand(command, wrapper);
				if (wrapper.Transaction != null) {
					return DoExecuteReader(command, CommandBehavior.Default);
				}
				else {
					return DoExecuteReader(command, CommandBehavior.CloseConnection);
				}
			}
			catch {
				wrapper.Connection.Close();
				throw;
			}
		}

		//Public Overridable Function ExecuteReader(ByVal command As DbCommand, ByVal transaction As DbTransaction) As DbDataReader
		//    PrepareCommand(command, transaction)
		//    Return Me.DoExecuteReader(command, CommandBehavior.Default)
		//End Function

		//Public Function ExecuteReader(ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As DbDataReader
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteReader(command)
		//    End Using
		//End Function

		//Public Function ExecuteReader(ByVal transaction As DbTransaction, ByVal commandType As CommandType, ByVal commandText As String) As DbDataReader
		//    Using command As DbCommand = Me.CreateCommandByCommandType(commandType, commandText)
		//        Return Me.ExecuteReader(command, transaction)
		//    End Using
		//End Function

		//Public Function ExecuteReader(ByVal transaction As DbTransaction, ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As DbDataReader
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteReader(command, transaction)
		//    End Using
		//End Function
#endregion
#region "ExecuteDataSet"
		public virtual DataSet ExecuteDataSet(CommandType commandType, string commandText)
		{
			using (DbCommand command = CreateCommandByCommandType(commandType, commandText)) {
				return ExecuteDataSet(command);
			}
		}

		public virtual DataSet ExecuteDataSet(DbCommand command)
		{
			DataSet dataSet = new DataSet();
			dataSet.Locale = CultureInfo.InvariantCulture;
			LoadDataSet(command, dataSet, "Table");
			return dataSet;
		}

		//Public Overridable Function ExecuteDataSet(ByVal command As DbCommand, ByVal transaction As DbTransaction) As DataSet
		//    Dim dataSet As New DataSet
		//    dataSet.Locale = CultureInfo.InvariantCulture
		//    Me.LoadDataSet(command, dataSet, "Table", transaction)
		//    Return dataSet
		//End Function

		//Public Overridable Function ExecuteDataSet(ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As DataSet
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteDataSet(command)
		//    End Using
		//End Function

		//Public Overridable Function ExecuteDataSet(ByVal transaction As DbTransaction, ByVal commandType As CommandType, ByVal commandText As String) As DataSet
		//    Using command As DbCommand = Me.CreateCommandByCommandType(commandType, commandText)
		//        Return Me.ExecuteDataSet(command, transaction)
		//    End Using
		//End Function

		//Public Overridable Function ExecuteDataSet(ByVal transaction As DbTransaction, ByVal storedProcedureName As String, ByVal ParamArray parameterValues As Object()) As DataSet
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Return Me.ExecuteDataSet(command, transaction)
		//    End Using
		//End Function
#endregion
#region "LoadDataSet"
		public virtual void LoadDataSet(DbCommand command, DataSet dataSet, string tableName)
		{
			LoadDataSet(command, dataSet, new string[] { tableName });
		}

		public virtual void LoadDataSet(CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
		{
			using (DbCommand command = CreateCommandByCommandType(commandType, commandText)) {
				LoadDataSet(command, dataSet, tableNames);
			}
		}

		public virtual void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames)
		{
			using (ConnectionWrapper wrapper = GetOpenConnection()) {
				PrepareCommand(command, wrapper);
				DoLoadDataSet(command, dataSet, tableNames);
			}
		}

		//Public Overridable Sub LoadDataSet(ByVal command As DbCommand, ByVal dataSet As DataSet, ByVal tableName As String, ByVal transaction As DbTransaction)
		//    Me.LoadDataSet(command, dataSet, New String() {tableName}, transaction)
		//End Sub

		//Public Overridable Sub LoadDataSet(ByVal command As DbCommand, ByVal dataSet As DataSet, ByVal tableNames As String(), ByVal transaction As DbTransaction)
		//    PrepareCommand(command, transaction)
		//    Me.DoLoadDataSet(command, dataSet, tableNames)
		//End Sub

		//Public Overridable Sub LoadDataSet(ByVal storedProcedureName As String, ByVal dataSet As DataSet, ByVal tableNames As String(), ByVal ParamArray parameterValues As Object())
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Me.LoadDataSet(command, dataSet, tableNames)
		//    End Using
		//End Sub

		//Public Sub LoadDataSet(ByVal transaction As DbTransaction, ByVal commandType As CommandType, ByVal commandText As String, ByVal dataSet As DataSet, ByVal tableNames As String())
		//    Using command As DbCommand = Me.CreateCommandByCommandType(commandType, commandText)
		//        Me.LoadDataSet(command, dataSet, tableNames, transaction)
		//    End Using
		//End Sub

		//Public Overridable Sub LoadDataSet(ByVal transaction As DbTransaction, ByVal storedProcedureName As String, ByVal dataSet As DataSet, ByVal tableNames As String(), ByVal ParamArray parameterValues As Object())
		//    Using command As DbCommand = Me.GetStoredProcCommand(storedProcedureName, parameterValues)
		//        Me.LoadDataSet(command, dataSet, tableNames, transaction)
		//    End Using
		//End Sub
#endregion
#region "UpdateDataSet"
		//Protected Overridable Sub SetUpRowUpdatedEvent(ByVal adapter As DbDataAdapter)
		//End Sub

		//Public Function UpdateDataSet(ByVal dataSet As DataSet, ByVal tableName As String, ByVal insertCommand As DbCommand, ByVal updateCommand As DbCommand, ByVal deleteCommand As DbCommand) As Integer
		//    Return Me.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, Nothing, Nothing)
		//End Function

		//Public Function UpdateDataSet(ByVal dataSet As DataSet, ByVal tableName As String, ByVal insertCommand As DbCommand, ByVal updateCommand As DbCommand, ByVal deleteCommand As DbCommand, ByVal transaction As DbTransaction) As Integer
		//    Return Me.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, transaction, Nothing)
		//End Function

		//Public Function UpdateDataSet(ByVal dataSet As DataSet, ByVal tableName As String, ByVal insertCommand As DbCommand, ByVal updateCommand As DbCommand, ByVal deleteCommand As DbCommand, ByVal updateBatchSize As Nullable(Of Integer)) As Integer
		//    Using wrapper As ConnectionWrapper = Me.GetOpenConnection
		//        'If ((updateBehavior = updateBehavior.Transactional) AndAlso (Transaction.Current Is Nothing)) Then
		//        '    Dim transaction As DbTransaction = Me.BeginTransaction(wrapper.Connection)
		//        '    Try
		//        '        Dim num As Integer = Me.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, transaction, updateBatchSize)
		//        '        Me.CommitTransaction(transaction)
		//        '        Return num
		//        '    Catch
		//        '        Me.RollbackTransaction(transaction)
		//        '        Throw
		//        '    End Try
		//        'End If
		//        If (Not insertCommand Is Nothing) Then
		//            PrepareCommand(insertCommand, wrapper)
		//        End If
		//        If (Not updateCommand Is Nothing) Then
		//            PrepareCommand(updateCommand, wrapper)
		//        End If
		//        If (Not deleteCommand Is Nothing) Then
		//            PrepareCommand(deleteCommand, wrapper)
		//        End If
		//        Return Me.DoUpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, updateBatchSize)
		//    End Using
		//End Function

		//Public Function UpdateDataSet(ByVal dataSet As DataSet, ByVal tableName As String, ByVal insertCommand As DbCommand, ByVal updateCommand As DbCommand, ByVal deleteCommand As DbCommand, ByVal transaction As DbTransaction, ByVal updateBatchSize As Nullable(Of Integer)) As Integer
		//    If (Not insertCommand Is Nothing) Then
		//        PrepareCommand(insertCommand, transaction)
		//    End If
		//    If (Not updateCommand Is Nothing) Then
		//        PrepareCommand(updateCommand, transaction)
		//    End If
		//    If (Not deleteCommand Is Nothing) Then
		//        PrepareCommand(deleteCommand, transaction)
		//    End If
		//    Return Me.DoUpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, updateBatchSize)
		//End Function
#endregion
#region "DoExecute"
		protected int DoExecuteNonQuery(DbCommand command)
		{
			try {
				int rowsAffected = command.ExecuteNonQuery();
				_instrumentation.CommandExecuted(command);
				return rowsAffected;
			}
			catch (Exception exception) {
				_instrumentation.CommandFailed(command, exception);
				throw;
			}
		}

		private DbDataReader DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior)
		{
			try {
				DbDataReader reader = command.ExecuteReader(cmdBehavior);
				_instrumentation.CommandExecuted(command);
				return reader;
			}
			catch (Exception exception) {
				_instrumentation.CommandFailed(command, exception);
				throw;
			}
		}

		private object DoExecuteScalar(DbCommand command)
		{
			try {
				object returnValue = command.ExecuteScalar();
				_instrumentation.CommandExecuted(command);
				return returnValue;
			}
			catch (Exception exception) {
				_instrumentation.CommandFailed(command, exception);
				throw;
			}
		}

		private void DoLoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames)
		{
			if ((tableNames == null)) {
				throw new ArgumentNullException("tableNames");
			}
			if ((tableNames.Length == 0)) {
				throw new ArgumentException("tableNames");
			}
			for (int i = 0; i <= tableNames.Length - 1; i++) {
				if (string.IsNullOrEmpty(tableNames[i])) {
					throw new ArgumentException("tableNames[" + i + "]");
				}
			}
			using (DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter()) {
				adapter.SelectCommand = command;
				try {
					string text = "Table";
					for (int j = 0; j <= tableNames.Length - 1; j++) {
						string sourceTable;
						if (j == 0) {
							sourceTable = text;
						}
						else {
							sourceTable = text + j;
						}
						adapter.TableMappings.Add(sourceTable, tableNames[j]);
					}
					adapter.Fill(dataSet);
					_instrumentation.CommandExecuted(command);
				}
				catch (Exception exception) {
					_instrumentation.CommandFailed(command, exception);
					throw;
				}
			}
		}

		//Private Function DoUpdateDataSet(ByVal dataSet As DataSet, ByVal tableName As String, ByVal insertCommand As DbCommand, ByVal updateCommand As DbCommand, ByVal deleteCommand As DbCommand, ByVal updateBatchSize As Nullable(Of Integer)) As Integer
		//    If String.IsNullOrEmpty(tableName) Then
		//        Throw New ArgumentException("tableName")
		//    End If
		//    If (dataSet Is Nothing) Then
		//        Throw New ArgumentNullException("dataSet")
		//    End If
		//    If (((insertCommand Is Nothing) AndAlso (updateCommand Is Nothing)) AndAlso (deleteCommand Is Nothing)) Then
		//        Throw New ArgumentException("ExceptionMessageUpdateDataSetArgumentFailure")
		//    End If
		//    Using adapter As DbDataAdapter = Me._dbProviderFactory.CreateDataAdapter
		//        Dim adapter2 As IDbDataAdapter = adapter
		//        If (Not insertCommand Is Nothing) Then
		//            adapter2.InsertCommand = insertCommand
		//        End If
		//        If (Not updateCommand Is Nothing) Then
		//            adapter2.UpdateCommand = updateCommand
		//        End If
		//        If (Not deleteCommand Is Nothing) Then
		//            adapter2.DeleteCommand = deleteCommand
		//        End If
		//        If updateBatchSize.HasValue Then
		//            adapter.UpdateBatchSize = updateBatchSize.Value
		//            If (Not insertCommand Is Nothing) Then
		//                adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None
		//            End If
		//            If (Not updateCommand Is Nothing) Then
		//                adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None
		//            End If
		//            If (Not deleteCommand Is Nothing) Then
		//                adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None
		//            End If
		//        End If
		//        Try
		//            Dim startTime As DateTime = DateTime.Now
		//            Dim num As Integer = adapter.Update(dataSet.Tables.Item(tableName))
		//            'Me.instrumentationProvider.FireCommandExecutedEvent(startTime)
		//            Return num
		//        Catch exception As Exception
		//            'Me.instrumentationProvider.FireCommandFailedEvent(("DbDataAdapter.Update() " & tableName), Me.ConnectionStringNoCredentials, exception)
		//            Throw
		//        End Try
		//    End Using
		//End Function
#endregion
#region "ConnectionWrapper"
		// Nested Type
		protected class ConnectionWrapper : IDisposable
		{

			private DbConnection _connection;
			private DbTransaction _transaction;
			private bool _disposeConnection;

			public DbConnection Connection {
				get { return _connection; }
			}

			public DbTransaction Transaction {
				get { return _transaction; }
			}

			public ConnectionWrapper(DbTransaction trans)
			{
				_transaction = trans;
				_connection = trans.Connection;
				_disposeConnection = false;
			}

			public ConnectionWrapper(DbConnection connection, bool disposeConnection)
			{
				_connection = connection;
				_disposeConnection = disposeConnection;
			}

			public void Dispose()
			{
				if (_disposeConnection) {
					_connection.Dispose();
				}
			}
		}
#endregion

	}
}
